import re
import time
import pymysql
import xlwt
from dateutil.parser import parse
from datetime import datetime
import datetime
from Db_interface import DB_interface


class CreateImportOrder():

    def __init__(self):
        self.db = DB_interface().exploit_db()  # 开发数据库
        self.cursor = self.db.cursor()
        self.db2 = DB_interface().online_db()  # 线上数据库
        self.cursor2 = self.db2.cursor()
        self.txt = 'sqh.txt'
        self.info_list = []
        self.total_money = {'办登': 0, '受通': 0, '年费': 0}

    def get_bd_sqh(self):
        sql = "select sqh from registration_fee_copy1 where sqh !=''"
        self.cursor2.execute(sql)
        content = self.cursor2.fetchall()
        bd_sqh_li = []
        for i in content:
            sqh = i[0]
            bd_sqh_li.append(sqh)
        return bd_sqh_li

    def get_late_sqh(self):
        sql = "select application_number from overdue_fine group by application_number"
        self.cursor2.execute(sql)
        content = self.cursor2.fetchall()
        zhinajin_sqh_list = []
        for i in content:
            zhinajin_sqh_list.append(i[0])
        return zhinajin_sqh_list

    def get_late_information(self, sqh, now):
        """获取缴费日期还没过期的数据"""
        sql = f"SELECT payment_start_time,payment_end_time,current_annual_fee,late_fee,total FROM overdue_fine WHERE application_number='{sqh}' AND  payment_end_time >= '{now}' ORDER BY payment_end_time LIMIT 1"
        self.cursor2.execute(sql)
        late_information = self.cursor2.fetchall()
        print("=====+++++++++++++++++; ", sqh, late_information)
        # 超出日期的按最后一个
        if not late_information:
            print('超出日期的按最后一个')
            sql = f"select payment_start_time,payment_end_time,current_annual_fee,late_fee,total from overdue_fine where application_number='{sqh}' and payment_end_time=(select max(payment_end_time) from overdue_fine group by application_number HAVING application_number='{sqh}')"
            self.cursor2.execute(sql)
            late_information = self.cursor2.fetchall()
        return late_information

    def get_money_information(self, sqh):
        sql = "select money_type,money,close_date,pay_status from annual_fee_information where application_number ='{}'".format(
            sqh)
        self.cursor2.execute(sql)
        money_info = self.cursor2.fetchall()
        return money_info

    def get_sqh(self):
        with open('sqh.txt', 'r') as f:
            content = f.read()
            f.close()
        sqh_list = content.split('\n')
        return sqh_list

    def get_yjf(self, sqh, money_type_content):
        money_type = money_type_content[0][0]
        num = re.findall("第(.*?)年", money_type)
        last_num = int(num[0]) - 1
        sql = f"SELECT cost_category FROM `big_data`.`by_payment_information` WHERE `application_number` = '{sqh}' limit 2"
        self.cursor2.execute(sql)
        yj_type = self.cursor2.fetchall()
        year_li = []
        for i in yj_type:
            try:
                old_year = re.findall("第(.*?)年", i[0])
                year_li.append(int(old_year[0]))
            except:
                year_li.append("未查到年费类型信息")

        if last_num in year_li:
            return money_type_content
        else:
            return (("该案子年费类型有异常, 请手动查询一下该案子的应缴费类型！！！！！！！！！！！！！！", ),)

    def get_sqh_money_type(self, sqh):
        sql = f"select money_type from annual_fee_information where application_number='{sqh}' and close_date=(select min(close_date) from annual_fee_information group by application_number HAVING application_number='{sqh}')"
        self.cursor2.execute(sql)
        money_type_content = self.cursor2.fetchall()
        money_type_content = self.get_yjf(sqh, money_type_content)
        return money_type_content

    def parse_late_information(self, late_information, money_type_content, sqh):
        print("===============================", late_information, money_type_content, sqh)
        year_money_num = late_information[0][2]
        late_money_num = late_information[0][3]
        late_money_type = '滞纳金'
        year_money_type = '年费'
        for i in money_type_content:
            if '滞纳金' in i[0]:
                late_money_type = i[0]
            elif '年年费' in i[0]:
                year_money_type = i[0]
            else:
                year_money_type = i[0]
                print('年费滞纳金类型异常')

        if int(float(year_money_num)) != 0:
            year_item = {'sqh': sqh, 'money_type': year_money_type, 'money_num': int(float(year_money_num))}
            self.info_list.append(year_item)
            self.total_money['年费'] += int(float(year_money_num))

        if int(float(late_money_num)) != 0:
            late_item = {'sqh': sqh, 'money_type': late_money_type, 'money_num': int(float(late_money_num))}
            self.info_list.append(late_item)
            self.total_money['年费'] += int(float(late_money_num))

    def get_earliest_date(self, money_info):
        date_li = []
        is_bd = False
        is_st = False
        is_late = False
        type = '年费'
        for i in money_info:
            if i[3] != '费足额':
                date_li.append(i[2])
            if '印花税' in i[0]:
                is_bd = True
            elif '申请费' in i[0]:
                is_st = True
            elif '公布印刷费' in i[0]:
                is_st = True
            elif '实质审查费' in i[0]:
                is_st = True
            elif '滞纳金' in i[0]:
                is_late = True
        if is_bd:
            type = '办登'
        if is_st:
            type = '受通'
        if is_late:
            type = '滞纳金'
        if len(date_li) > 0:
            earliest_date = min(date_li)
        else:
            earliest_date = ''
        return earliest_date, type

    def classify_expenses(self, money_info, type, earliest_date, sqh):
        sqh_item = {}
        if type == '年费':
            sqh_item['money_li'] = []
            sqh_item['money_num'] = 0
            sqh_item['status_li'] = []
            sqh_item['type'] = type
            for i in money_info:
                close_date = i[2]
                if close_date == earliest_date:
                    year_money_type = i[0]
                    year_money_num = i[1]
                    year_item = {'sqh': sqh, 'money_type': year_money_type, 'money_num': int(float(year_money_num))}
                    if year_item not in self.info_list:
                        self.info_list.append(year_item)
                        self.total_money['年费'] += int(float(year_money_num))

        elif type == '滞纳金':
            final_close_date = ''
            for j in money_info:
                if '滞纳金' in j[0]:
                    final_close_date = j[2]
            self.generate_late_fee(final_close_date, money_info, earliest_date, sqh)

        elif type == '办登':
            sqh_item['money_li'] = []
            sqh_item['money_num'] = 0
            sqh_item['status_li'] = []
            sqh_item['type'] = type
            for i in money_info:
                money_status = i[3]
                if money_status != '费足额':
                    year_money_type = i[0]
                    year_money_num = i[1]
                    year_item = {'sqh': sqh, 'money_type': year_money_type, 'money_num': int(float(year_money_num))}
                    if year_item not in self.info_list:
                        self.info_list.append(year_item)
                        self.total_money['办登'] += int(float(year_money_num))

        elif type == '受通':
            sqh_item['money_li'] = []
            sqh_item['money_num'] = 0
            sqh_item['status_li'] = []
            sqh_item['type'] = type
            sqh_item['close_date'] = earliest_date
            for i in money_info:
                money_status = i[3]
                if money_status != '费足额':
                    year_money_type = i[0]
                    year_money_num = i[1]
                    year_item = {'sqh': sqh, 'money_type': year_money_type, 'money_num': int(float(year_money_num))}
                    if year_item not in self.info_list:
                        self.info_list.append(year_item)
                        self.total_money['受通'] += int(float(year_money_num))

    def generate_late_fee(self, final_close_date, money_info, earliest_date, sqh):
        year_type = ''
        year_num = ''
        late_type = ''
        late_num = ''

        is_add = False
        for k in money_info:
            if k[2] == earliest_date:
                if "滞纳金" in k[0]:
                    late_type = k[0]
                    late_num = k[1]
                else:
                    year_type = k[0]
                    year_num = k[1]
        final_close_date = parse(final_close_date)
        NOW = parse(datetime.datetime.now().strftime("%Y-%m-%d"))
        for i in range(1, 6):
            delta = datetime.timedelta(days=30 * (6 - i))
            start_date = final_close_date - delta
            close_date = final_close_date - datetime.timedelta(days=30 * (5 - i))
            month_item = {}
            month_item['year_num'] = int(float(year_num))
            month_item['late_num'] = int(float(late_num)) / 5 * i
            month_item['total_num'] = month_item['year_num'] + month_item['late_num']
            month_item['start_date'] = start_date
            month_item['close_date'] = close_date

            if start_date < NOW <= close_date:
                month_item['year_num'] = year_num
                year_item = {'sqh': sqh, 'money_type': year_type, 'money_num': int(float(year_num))}
                self.info_list.append(year_item)
                self.total_money['年费'] += int(float(year_num))
                late_item = {'sqh': sqh, 'money_type': late_type, 'money_num': int(float(month_item['late_num']))}
                self.info_list.append(late_item)
                self.total_money['年费'] += int(float(month_item['late_num']))
                is_add = True
        if not is_add:
            i = 5
            delta = datetime.timedelta(days=30 * (6 - i))
            start_date = final_close_date - delta
            close_date = final_close_date - datetime.timedelta(days=30 * (5 - i))
            month_item = {}
            month_item['year_num'] = int(float(year_num))
            month_item['late_num'] = int(float(late_num)) / 5 * i
            month_item['total_num'] = month_item['year_num'] + month_item['late_num']
            month_item['start_date'] = start_date
            month_item['close_date'] = close_date
            month_item['year_num'] = year_num
            year_item = {'sqh': sqh, 'money_type': year_type, 'money_num': int(float(year_num))}
            self.info_list.append(year_item)
            self.total_money['年费'] += int(float(year_num))
            late_item = {'sqh': sqh, 'money_type': late_type, 'money_num': int(float(month_item['late_num']))}
            self.info_list.append(late_item)
            self.total_money['年费'] += int(float(month_item['late_num']))

    def get_bd_money_info(self, sqh):
        sql = f"select money_str,close_date,total,is_equal from registration_fee_copy1 where sqh='{sqh}'"
        self.cursor2.execute(sql)
        get_bd_money_content = self.cursor2.fetchall()
        return get_bd_money_content

    def parse_bd_content(self, sqh, bd_money_content):
        # (('第2年度年费90.0元+印花税5.0元', '2021-10-08', '95.0', '是', 0),)
        money_str = bd_money_content[0][0].replace("+印花税5.0元", '')
        if sqh[4] == "1":
            patent_type = '发明'
        elif sqh[4] == "2":
            patent_type = '实用新型'
        elif sqh[4] == "3":
            patent_type = '外观设计'
        else:
            patent_type = ''
        type_str = patent_type + '专利第'
        money_str = money_str.replace('.0元', '').replace('第', type_str).replace('年度', '年')
        year_money_type = money_str.split('费')[0] + '费'
        year_money_num = money_str.split('费')[1]
        print("sqh: ", sqh)
        year_item = {'sqh': sqh, 'money_type': year_money_type, 'money_num': int(float(year_money_num))}
        if year_item not in self.info_list:
            self.info_list.append(year_item)
            self.total_money['办登'] += int(float(year_money_num))

    def parse_late(self, sqh, now):
        late_information = self.get_late_information(sqh, now)
        money_type_content = self.get_sqh_money_type(sqh)
        self.parse_late_information(late_information, money_type_content, sqh)

    def parse_bd(self, sqh):
        print('根据每日办登生成')
        bd_money_content = self.get_bd_money_info(sqh)
        self.parse_bd_content(sqh, bd_money_content)

    def parse_other(self, sqh, money_info, earliest_date, type):
        self.classify_expenses(money_info, type, earliest_date, sqh)

    def insert_warning_table(self, sqh, sqh_item):
        now_time = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
        sql = f"INSERT INTO MoneyWarning(sqh, order_number,money_str,money_num,status_str,close_date,liaisons,tele,customer_status,customer_money_num,customer_pay_date,customer_money_detail,company_is_paid,company_paid_detail,company_paid_date,now_days_left,customer_days_left,case_status,case_name,sq_date,pantent_user,agency,first_agent,fmr,port,type,update_time) " \
              f"VALUES ('{sqh}', '{sqh_item['order_number']}','{'+'.join(sqh_item['money_li'])}','{sqh_item['money_num']}','{';'.join(sqh_item['status_li'])}','{sqh_item['close_date']}','{sqh_item['liaisons']}','{sqh_item['tele']}','{sqh_item['customer_status']}','{sqh_item['customer_money_num']}','{sqh_item['customer_pay_date']}','{sqh_item['customer_money_detail']}','{sqh_item['company_is_paid']}','{sqh_item['company_paid_detail']}','{sqh_item['company_paid_date']}','{sqh_item['now_days_left']}','{sqh_item['customer_days_left']}','{sqh_item['case_status']}','{sqh_item['case_name']}','{sqh_item['sq_date']}','{sqh_item['pantent_user']}','{sqh_item['agency']}','{sqh_item['first_agent']}','{sqh_item['fmr']}','{sqh_item['port']}','{sqh_item['type']}','{now_time}');"
        try:
            self.cursor2.execute(sql)
            self.db2.commit()
        except Exception as e:
            print(e, sql)

    def update_warning_table(self, sqh, sqh_item):
        now_time = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
        sql = f"update MoneyWarning set money_str='{'+'.join(sqh_item['money_li'])}' , money_num='{sqh_item['money_num']}' , status_str='{';'.join(sqh_item['status_li'])}' , close_date='{sqh_item['close_date']}' , customer_status='{sqh_item['customer_status']}' , customer_money_num='{sqh_item['customer_money_num']}' , customer_pay_date='{sqh_item['customer_pay_date']}' , customer_money_detail='{sqh_item['customer_money_detail']}' , company_is_paid='{sqh_item['company_is_paid']}' , company_paid_detail='{sqh_item['company_paid_detail']}' , company_paid_date='{sqh_item['company_paid_date']}' , now_days_left='{sqh_item['now_days_left']}' , customer_days_left='{sqh_item['customer_days_left']}' , case_status='{sqh_item['case_status']}' , case_name='{sqh_item['case_name']}' , sq_date='{sqh_item['sq_date']}' , pantent_user='{sqh_item['pantent_user']}'  , agency='{sqh_item['agency']}'  , first_agent='{sqh_item['first_agent']}'  , fmr='{sqh_item['fmr']}'  , port='{sqh_item['port']}'  , type='{sqh_item['type']}'  , update_time='{now_time}' where sqh='{sqh}'"
        try:
            self.cursor2.execute(sql)
            self.db2.commit()
        except Exception as e:
            print(e, sql)

    def parse_is_paid(self, i):
        item = self.info_list[i]
        print(f"info_list[{i}]:", self.info_list[i])
        sql = f"select money_number,money_type from ccy_paid where application_number='{item['sqh']}'"
        self.cursor2.execute(sql)
        is_paid_content = self.cursor2.fetchall()
        for type in is_paid_content:
            if item['money_type'] == type[1]:
                print(item['sqh'], "缴费是否重复，请检查！")

    def save_excel(self):
        workbook = xlwt.Workbook()
        date = time.strftime("%Y-%m-%d", time.localtime())
        sheet = workbook.add_sheet("费用详情")
        borders = xlwt.Borders()
        borders.left = xlwt.Borders.THIN  # 对边框对象进行操作，指定边框上下左右的边框类型为虚线
        borders.right = xlwt.Borders.THIN
        borders.top = xlwt.Borders.THIN
        borders.bottom = xlwt.Borders.THIN
        borders.left_colour = 0x40  # 指定上下左右的边框颜色为0x40
        borders.right_colour = 0x40
        borders.top_colour = 0x40
        borders.bottom_colour = 0x40
        style = xlwt.XFStyle()  # Create Style   #创建样式对象
        style.borders = borders
        sheet.write(0, 0, '序号', style)
        sheet.write(0, 1, '申请号/专利号/国际申请号/海牙转交编号', style)
        sheet.write(0, 2, '业务类型', style)
        sheet.write(0, 3, '票据抬头', style)
        sheet.write(0, 4, '统一社会信用代码', style)
        sheet.write(0, 5, '费用种类', style)
        sheet.write(0, 6, '外币金额', style)
        sheet.write(0, 7, xlwt.Formula('"费用金额（"&SUM(H2:H64102)&"人民币)"'), style)
        sheet.write(0, 8, '备注', style)

        for i in range(len(self.info_list)):
            self.parse_is_paid(i)
            if self.info_list[i]['sqh'][4] == "1":
                yewu_type = "发明"
            elif self.info_list[i]['sqh'][4] == '2':
                yewu_type = "实用新型"
            elif self.info_list[i]['sqh'][4] == '3':
                yewu_type = '外观设计'
            else:
                yewu_type = ""

            if "滞纳金" in self.info_list[i]['money_type']:
                MoneyType = yewu_type + "专利年费滞纳金"
            else:
                MoneyType = self.info_list[i]['money_type']

            sheet.write(i + 1, 0, i + 1, style)
            sheet.write(i + 1, 1, self.info_list[i]['sqh'], style)
            sheet.write(i + 1, 2, yewu_type, style)
            sheet.write(i + 1, 3, '武汉信合红谷知识产权代理事务所（特殊普通合伙）', style)
            sheet.write(i + 1, 4, '91420100MA4K4BRC4X', style)
            sheet.write(i + 1, 5, MoneyType, style)
            sheet.write(i + 1, 6, '', style)
            sheet.write(i + 1, 7, self.info_list[i]['money_num'], style)
            sheet.write(i + 1, 8, '', style)
        sheet.col(0).width = 1500
        sheet.col(1).width = 3600
        sheet.col(2).width = 12000
        sheet.col(3).width = 6000
        sheet.col(4).width = 6000
        sheet.col(5).width = 6000
        sheet.col(6).width = 3000
        workbook.save('./save_excel/{}官费缴纳汇总表.xls'.format(date))

    def get_sqh_li(self):
        late_sqh_list = self.get_late_sqh()  # 拿到滞纳金申请号列表
        bd_sqh_li = self.get_bd_sqh()  # 拿到办登申请号列表
        sqh_content = self.get_sqh()  # sqh.txt的申请号
        now = datetime.datetime.now().strftime("%Y-%m-%d")
        total_sqh_count = len(sqh_content)
        return late_sqh_list, sqh_content, bd_sqh_li, now, total_sqh_count

    def run(self):
        late_sqh_list, sqh_content, bd_sqh_li, now, total_sqh_count = self.get_sqh_li()
        for i in range(0, total_sqh_count):
            sqh = sqh_content[i].strip('ZL')
            print(f'sqh: {sqh} , 共{total_sqh_count}个，第{i + 1}个')
            if sqh in late_sqh_list:
                self.parse_late(sqh, now)
            else:
                money_info = self.get_money_information(sqh)
                if not money_info:
                    if sqh not in bd_sqh_li:
                        print(sqh, '未找到缴费信息')
                        year_item = {'sqh': sqh, 'money_type': '未找到缴费信息', 'money_num': 0}
                        self.info_list.append(year_item)
                    else:
                        self.parse_bd(sqh)
                else:
                    earliest_date, type = self.get_earliest_date(money_info)
                    if not earliest_date:
                        print('没有需要缴费的')
                        year_item = {'sqh': sqh, 'money_type': '没有需要缴费的', 'money_num': 0}
                        self.info_list.append(year_item)
                    else:
                        self.parse_other(sqh, money_info, earliest_date, type)
        self.total_money['共计'] = self.total_money['办登'] + self.total_money['受通'] + self.total_money['年费']
        self.save_excel()
        print('\n', self.total_money)
        self.db.close()
        self.db2.close()


if __name__ == '__main__':
    url = "https://cpquery.cponline.cnipa.gov.cn/detail/index?zhuanlisqh=202120215288X&anjianbh"
    CreateImportOrder().run()
